/**
 * @ClassName Student
 * @Description: TODO
 * @Author: MaykkSTAR@163.com
 * @DATE: 2023/2/13
 */

import java.sql.*;

public class StudentDormitoryManagementSystem {
    private static final String URL = "jdbc:mysql://localhost:3306/xiaoma";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xiaoma", USERNAME, PASSWORD);

        createTable(connection);

        // 插入学生信息
        insertStudent(connection, "123456", "John Doe", 22, "123-456-7890", "A101");
        insertStudent(connection, "234567", "Jane Doe", 21, "234-567-8901", "A102");

        // 查询所有学生信息
        ResultSet resultSet = selectAllStudents(connection);
        while (resultSet.next()) {
            System.out.println(
                    "学号：" + resultSet.getString("student_id") +
                            "，姓名：" + resultSet.getString("name") +
                            "，年龄：" + resultSet.getInt("age") +
                            "，联系方式：" + resultSet.getString("contact") +
                            "，宿舍号：" + resultSet.getString("dormitory_number"));
        }
    }

    private static void createTable(Connection connection) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS students (" +
                "student_id VARCHAR(10) PRIMARY KEY," +
                "name VARCHAR(50)," +
                "age INT," +
                "contact VARCHAR(20)," +
                "dormitory_number VARCHAR(10)" +
                ")";

        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        System.out.println("学生信息表创建成功！");
    }

    private static void insertStudent(Connection connection, String studentId, String name, int age, String contact, String dormitoryNumber) throws SQLException {
        String sql = "INSERT INTO students (student_id, name, age, contact, dormitory_number) " +
                "VALUES (?, ?, ?, ?, ?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, studentId);
        preparedStatement.setString(2, name);
        preparedStatement.setInt(3, age);
        preparedStatement.setString(4, contact);
        preparedStatement.setString(5, dormitoryNumber);
        preparedStatement.executeUpdate();
        System.out.println("学生信息插入成功！");
    }

    private static ResultSet selectAllStudents(Connection connection) throws SQLException {
        String sql = "SELECT * FROM students";

        Statement statement = connection.createStatement();
        return statement.executeQuery(sql);
    }
}
